const express = require('express') //1.引入express
const router = express.Router() //2.引入路由
const { db, genid } = require('../db/DbUtils') //引入雪花id和数据库

// 添加修改删除查询博客


// 当篇文章的内容
router.get("/detail", async (req, res) => {
  //async await缺一不可，不然会报错
  let { id } = req.query
  let detail_sql = "SELECT * FROM `bolg` WHERE `id` = ? "
  let { err, rows } = await db.async.all(detail_sql, [id]);

  if (err == null) {
    res.send({
      code: 200,
      msg: "获取成功",
      rows
    })
  } else {
    res.send({
      code: 500,
      msg: "获取失败"
    })
  }

})
// 添加
router.post('/add', async (req, res) => {
  //async允许异步执行
  let { title, categoryId, content } = req.body //接收前端的请求
  let id = genid.NextId()
  let create_time = new Date().getTime() //时间戳
  const insert_sql =
    'INSERT INTO `bolg`(`id`,`title`,`category_id`,`content`,`create_time`) VALUES(?,?,?,?,?)'
  let params = [id, title, categoryId, content, create_time]
  let { err, rows } = await db.async.run(insert_sql, params)
  console.log(err)
  if (err == null) {
    res.send({
      code: 200,
      msg: '添加成功',
    })
  } else {
    res.send({
      code: 500,
      msg: '添加失败',
    })
  }
})

// 修改
router.put('/_token/update', async (req, res) => {
  //async允许异步执行
  // let out = await db.async.all("select * from `admin`", []);
  let { id, title, categoryId, content } = req.body //接收前端的请求
  let create_time = new Date().getTime() //时间戳
  const update_sql =
    'UPDATE `bolg` SET `title` = ?,`content` = ?,`category_id` = ? WHERE `id` = ?'

  let params = [title, content, categoryId, id]
  // params这里是数组赋值[]，不是{}
  let { err, rows } = await db.async.run(update_sql, params)
  console.log(err)
  if (err == null) {
    res.send({
      code: 200,
      msg: '修改成功',
    })
  } else {
    res.send({
      code: 500,
      msg: '修改失败',
    })
  }
})

// 删除  /bolg/delete?id=xxx
router.delete('/_token/delete', async (req, res) => {
  //async允许异步执行
  let id = req.query.id //接收前端的请求
  const delete_sql = 'DELETE FROM `bolg` WHERE `id` = ?'
  let { err, rows } = await db.async.run(delete_sql, [id])
  if (err == null) {
    res.send({
      code: 200,
      msg: '修改成功',
    })
  } else {
    res.send({
      code: 500,
      msg: '修改失败',
    })
  }
})

// 查询博客
router.get('/search', async (req, res) => {
  // keyword关键字，在文章的文章和标题都查得出来，还有分类id，存在就查询，不存在就不查询
  // categoryId分类id
  // 分页：page页码，pageSize分页大小
  let { keyword, categoryId, page, pageSize } = req.query
  page = page == null ? 1 : page //这里是后端做一个判断，如果前端没传值，后端也有一个默认的值，不至于太乱
  pageSize = pageSize == null ? 10 : pageSize
  categoryId = categoryId == null ? 0 : categoryId
  keyword = keyword == null ? '' : keyword

  let params = []
  let whereSqls = []
  if (categoryId != 0) {
    whereSqls.push(" `category_id` = ?") // push() 方法可向数组的末尾添加一个或多个元素，并返回新的长度。
    params.push(categoryId)
  }
  if (keyword != "") {
    whereSqls.push(" (`title` LIKE ? OR `content` LIKE ?)")
    params.push("%" + keyword + "%")
    params.push("%" + keyword + "%")
  }
  let whereSqlStr = ""
  if (whereSqls.length > 0) {
    whereSqlStr = "WHERE" + whereSqls.join("AND")
    // join() 方法将数组作为字符串返回。数组名.join()
  }

  // 查分页数据 substr裁剪从0-50个字
  let searchSql =
    "SELECT `id`,`category_id`,`title`,`create_time`,substr(`content`,0,250) AS `content` FROM `bolg`" +
    whereSqlStr +
    "ORDER BY `create_time` DESC LIMIT ?,?"
  let searchSqlParams = params.concat([(page - 1) * pageSize, pageSize])
  // 1,10   1-1=0，从第0条，往后查十条  ，分页运算
  // concat() 方法用于连接两个或多个数组。不会更改现有数组，而是返回一个新数组，其中包含已连接数组的值。
  // 数组名1.concat(数组名2)
  // 总数 201  201/10 =21

  // 查询数据总数
  let searchCountSql = "SELECT count(*) as `count` FROM `bolg` " + whereSqlStr;
  let searchCountParams = params

  // 分页数据 数据库名.async.all ，传多个参数时候的一种请求方法
  let searchResult = await db.async.all(searchSql, searchSqlParams)
  let countResult = await db.async.all(searchCountSql, searchCountParams)
  console.log(countResult, countResult)
  if (searchResult.err == null && countResult.err == null) {
    res.send({
      code: 200,
      msg: '查询成功',
      data: {
        keyword,
        categoryId,
        page,
        pageSize,
        rows: searchResult.rows,
        count: countResult.rows[0].count,
        // count: countResult.rows[0]['count(*)'], //下标的形式取出来总数
      },
    })
  } else {
    res.send({
      code: 500,
      msg: '查询失败',
    })
  }
})

module.exports = router //3.导出路由

// 1.前端请求的数据要和后端的数据栏数数量一致  2.json数据结尾不能有  3.数据库表名写错了
